﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using ERP_RORZE_DLL.Product.DAO;
using ERP_RORZE_DLL.Product.database;
using ERP_RORZE_PERMISSION.Base;
using ERP_RORZE_PRODUCT.Product.database;
using System.Data.OleDb;
using ERP_RORZE_SYSTEM;
using System.Data.Common;
using System.Data.SqlClient;
using ERP_RORZE_DLL.Product.Base;

namespace ERP_RORZE_PRODUCT.Product.PartData
{
    public partial class ctrlMaterial : DevExpress.XtraEditors.XtraUserControl
    {
        ClsPartDataDAO clsPartdataDAO = new ClsPartDataDAO();
        DataTable dt;
        ClsMaterialTable obj = new ClsMaterialTable();
        private LogUtils log;

        public ctrlMaterial()
        {
            InitializeComponent();
            log = new LogUtils();
        }

        void Reload(object sender, EventArgs e)
        {
            dt = clsPartdataDAO.GetAllMaterial();
            dgvPartMaterial.DataSource = dt;
            gridView1.BestFitColumns();
        }
        private void ctrlMaterial_Load(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            obj.MaterialID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MaterialID").ToString();
            clsPartdataDAO.DeleteMaterial(gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MaterialID").ToString());

            //write Log
            log.Table = LogConstant.Table.Product.MaterialList;
            log.Task = LogConstant.Task.Delete;
            log.Description = "MaterialID: " + obj.MaterialID ;
            log.WriteLog();

            Reload(sender, e);
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            btDelete_Click(sender, e);
        }

        private void btnAddNew_Click(object sender, EventArgs e)
        {
            frm_MaterialAdd frmAdd = new frm_MaterialAdd();
            frmAdd.Reload += new EventHandler(Reload);
            frmAdd.Show();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            btnAddNew_Click(sender, e);
        }

        private void btEdit_Click(object sender, EventArgs e)
        {
            try
            {
                frm_MaterialEdit frmEdit = new frm_MaterialEdit();
                frmEdit.Density = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Density").ToString();
                frmEdit.MaterialID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MaterialID").ToString();
                frmEdit.Type = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Type").ToString();
                frmEdit.UnitPrice = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "UnitPrice").ToString();
                frmEdit.Reload += new EventHandler(Reload);
                frmEdit.Show();
            }
            catch
            { 
            }
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            btEdit_Click(sender, e);
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    OleDbCommand commandCount = new OleDbCommand("select count(*) from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet\
                    int count = (int) commandCount.ExecuteScalar();
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_Material]";

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("MaterialID", "MaterialID");
                    bulkInsert.ColumnMappings.Add(date);

                    SqlBulkCopyColumnMapping toolId = new SqlBulkCopyColumnMapping("Density", "Density");
                    bulkInsert.ColumnMappings.Add(toolId);

                    SqlBulkCopyColumnMapping makerId = new SqlBulkCopyColumnMapping("UnitPrice", "UnitPrice");
                    bulkInsert.ColumnMappings.Add(makerId);

                    SqlBulkCopyColumnMapping Type = new SqlBulkCopyColumnMapping("Type", "Type");
                    bulkInsert.ColumnMappings.Add(Type);

                    bulkInsert.WriteToServer(dr);

                    connection.Close();

                    //write Log
                    log.Table = LogConstant.Table.Product.MaterialList;
                    log.Task = LogConstant.Task.Import;
                    log.Description = string.Format(LogConstant.Message.Import, count.ToString(), "");
                    log.WriteLog();

                    Reload(sender, e);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }
            }
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            clsConstantsFuction.ExportToExcel(gridView1, 1, true);
        }
    }
}
